// Copyright (c) 2002 HTHK
package com.ffpc.sql;

import java.io.Serializable;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

/**
 * A Class class.
 * <P>
 * @author HTHK
 */
public class BaseSqlCommand {
  private Statement stmt;
  private ResultSet rs;
  private PreparedStatement pstmt;
  private Connection con;

  public BaseSqlCommand(Connection con) {
    this.con = con;
    stmt = null;
    pstmt = null;
    rs = null;
  }

  public void update(Row row) throws DataException {
    try {
      PrimaryKey pk = row.getPrimaryKey();
      UpdatableColumns uc = row.getUpdatableColumns();

      pstmt = con.prepareStatement(
          getSqlByPk(pk),
          ResultSet.TYPE_SCROLL_INSENSITIVE,
          ResultSet.CONCUR_UPDATABLE);
      if (pk == null || pk.getColumns() == null)
        throw new DataException("Primary key doesn't exist.");
      Column[] pkCols = pk.getColumns();
      for (int j = 0; j < pkCols.length; j++) {

//System.out.println(pkCols[j].getName()+"="+pkCols[j].getValue());

        setPreparedStatement(pstmt, j + 1, pkCols[j]);
      }
      rs = pstmt.executeQuery();
      Column[] columns = uc.getColumns();
      if (rs.absolute(1)) { // moves the cursor to the fifth row of rs; // moves cursor to the insert row

        for (int i = 0; i < columns.length; i++) {

          if (columns[i] != null) {
            System.out.println(i + "=" + columns[i].getName() + "=>" +
                               columns[i].getValue());
            updateColumn(rs, columns[i]);
          }
          else {
            System.out.println(i);
          }
        }
        rs.updateRow();
      }
      else {
        throw new DataException("The record doesn't exist in database.");
      }
    }
    catch (DataException e) {
      closeStatement();
      throw e;
    }
    catch (SQLException e) {
      closeStatement();
      throw new DataException(e);
    }
    catch (Exception e) {
      closeStatement();
      e.printStackTrace();
      throw new DataException(e);
    }
    finally {
      closeStatement();
    }
  }

  public void delete(Row row) throws DataException {
    try {
      PrimaryKey pk = row.getPrimaryKey();

System.out.println(getSqlByPk(pk));

      pstmt = con.prepareStatement(
          getSqlByPk(pk),
          ResultSet.TYPE_SCROLL_INSENSITIVE,
          ResultSet.CONCUR_UPDATABLE);
      Column[] pkCols = pk.getColumns();
      for (int j = 0; j < pkCols.length; j++) {
        setPreparedStatement(pstmt, j + 1, pkCols[j]);
        System.out.println(pkCols[j].getName()+"="+pkCols[j].getValue());
      }
      System.out.println("pk read done");
      rs = pstmt.executeQuery();

      if(rs == null) {       System.out.println("rs null");}
      if (rs.absolute(1)) { // moves the cursor to the fifth row of rs; // moves cursor to the insert row
        rs.deleteRow();
      }
      else {
        throw new DataException("error.sql.notExist");
      }
    }
    catch (DataException e) {
      closeStatement();
      e.printStackTrace();
      throw e;
    }
    catch (SQLException e) {
      closeStatement();
      e.printStackTrace();
      throw new DataException(e);
    }
    catch (Exception e) {
      closeStatement();
      e.printStackTrace();
      throw new DataException(e);
    }
    finally {
      closeStatement();
    }
  }

  public void insert(Row row) throws DataException {
    try {
      if (findByPk(row).size() > 0)
        throw new DataException("This record has already existed.");
      PrimaryKey pk = row.getPrimaryKey();
      stmt = con.createStatement(
          ResultSet.TYPE_SCROLL_INSENSITIVE,
          ResultSet.CONCUR_UPDATABLE);
          System.out.println(getNoRowsSql(pk));
      rs = stmt.executeQuery(getNoRowsSql(pk));

      Column[] columns = row.getColumns();
      rs.moveToInsertRow(); // moves cursor to the insert row
      for (int i = 0; i < columns.length; i++) {
        if (columns[i].getInvalidInput()) {
          System.out.println(columns[i].getName()+"="+columns[i].getValue());
          StringBuffer errorMapping = new StringBuffer("error.form.")
              .append(pk.getTableName())
              .append(".")
              .append(columns[i].getName());
          throw new DataException(errorMapping.toString());
        }
        updateColumn(rs, columns[i]);
      }
      rs.insertRow();
      rs.moveToCurrentRow();
    }
    catch (DataException e) {
      closeStatement();
      e.printStackTrace();
      throw e;
    }
    catch (SQLException e) {
      closeStatement();
      e.printStackTrace();
      throw new DataException(e);
    }
    catch (Exception e) {
      closeStatement();
      e.printStackTrace();
      throw new DataException(e);
    }
    finally {
      closeStatement();
    }
  }

  public Vector findByPk(Row row) throws DataException {
    Vector rows = new Vector();
    try {
      PrimaryKey pk = row.getPrimaryKey();
      pstmt = con.prepareStatement(
          getSqlByPk(pk),
          ResultSet.TYPE_SCROLL_INSENSITIVE,
          ResultSet.CONCUR_UPDATABLE);
      Column[] pkCols = pk.getColumns();
      for (int j = 0; j < pkCols.length; j++) {
        setPreparedStatement(pstmt, j + 1, pkCols[j]);
      }
      rs = pstmt.executeQuery();
      for (int j = 0; rs.next(); j++) {
        Column[] cols = row.createColumns();
        for (int i = 0; i < cols.length; i++) {
          cols[i].setValue(getColumnValue(rs, cols[i]));
        }
        rows.add(j, cols);
      }
    }
    catch (DataException e) {
      closeStatement();
      e.printStackTrace();
      throw e;
    }
    catch (SQLException e) {
      closeStatement();
      e.printStackTrace();
      throw new DataException(e);
    }
    catch (Exception e) {
      closeStatement();
      e.printStackTrace();
      throw new DataException(e);
    }
    finally {
      closeStatement();
    }
    return rows;
  }

  public Vector find(Row row) throws DataException {
    Vector rows = new Vector();
    try {

      pstmt = con.prepareStatement(getSql(row));
      Column[] columns = row.getColumns();
      int k = 1;
      for (int j = 0; j < columns.length; j++) {
        if (columns[j] != null && columns[j].getValue() != null) {
          setPreparedStatement(pstmt, k, columns[j]);
          k++;
        }
      }
      rs = pstmt.executeQuery();
      for (int j = 0; rs.next(); j++) {
        Column[] cols = row.createColumns();
        for (int i = 0; i < cols.length; i++) {
          cols[i].setValue(getColumnValue(rs, cols[i]));
        }
        rows.add(j, cols);
      }
    }
    catch (DataException e) {
      closeStatement();
      e.printStackTrace();
      throw e;
    }
    catch (SQLException e) {
      e.printStackTrace();
      closeStatement();
      throw new DataException(e);
    }
    catch (Exception e) {
      e.printStackTrace();
      closeStatement();
      throw new DataException(e);
    }
    finally {
      closeStatement();
    }
    return rows;
  }

  private String getSql(Row row) {
    String tableName = row.getPrimaryKey().getTableName();
    Column[] columns = row.getColumns();
    StringBuffer sql = new StringBuffer();
    sql = sql.append("SELECT ").append(tableName)
        .append(".* FROM ").append(tableName);
    StringBuffer whereClause = null;
    for (int i = 0; i < columns.length; i++) {
      if (columns[i] != null && columns[i].getValue() != null) {
        if (whereClause == null)
          whereClause = new StringBuffer(" where ");
        else
          whereClause = whereClause.append(" and ");
        if (columns[i].getType() == Column.Types.string)
          whereClause = whereClause.append(columns[i].getName()).append(
              " like ? ");
        else if (columns[i].getType() == Column.Types.integer)
          whereClause = whereClause.append(columns[i].getName()).append(" = ? ");
        else if (columns[i].getType() == Column.Types.number)
          whereClause = whereClause.append(columns[i].getName()).append(" = ? ");
        else if (columns[i].getType() == Column.Types.time)
          whereClause = whereClause.append("trunc(").append(columns[i].getName()).
              append(") = ? ");
        else if (columns[i].getType() == Column.Types.time_n)
          whereClause = whereClause.append("trunc(").append(columns[i].getName()).
              append(") = ? ");
      }
    }
    if (whereClause == null)
      whereClause = new StringBuffer();
      // append order
    PrimaryKey pk = row.getPrimaryKey();
    String[] keys = pk.getList();
    for (int i = 0; i < keys.length; i++) {
      if (i == 0)
        whereClause = whereClause.append(" order by ").append(keys[i]);
      else
        whereClause = whereClause.append(", ").append(keys[i]);
    }
    sql = sql.append(whereClause.toString());
    return sql.toString();
  }

  private String getSqlByPk(PrimaryKey pk) throws DataException {
    if (pk == null || pk.getColumns() == null || pk.getColumns().length == 0) {
      throw new DataException("error.sql.pk");
    }
    StringBuffer sql = new StringBuffer();

    sql.append("SELECT ");
    sql.append(pk.getTableName());
    sql.append(".* FROM ");
    sql.append(pk.getTableName());
    sql.append(" where ");
    sql.append(pk.getColumns()[0].getName());
    sql.append(" = ? ");

    for (int i = 1; i < pk.getColumns().length; i++) {
      sql.append(" and ").append(pk.getColumns()[i].getName()).append(
          " = ? ");
    }
    System.out.print(sql.toString());
    return sql.toString();
  }

  private String getNoRowsSql(PrimaryKey pk) throws DataException {
    if (pk == null || pk.getColumns() == null || pk.getColumns().length == 0)
      throw new DataException("error.sql.pk");
    StringBuffer sql = new StringBuffer();
    sql = sql.append("SELECT ").append(pk.getTableName())
        .append(".* FROM ").append(pk.getTableName())
        .append(" where ").append(pk.getColumns()[0].getName()).append(
        " is null ");
    return sql.toString();
  }

  private void setPreparedStatement(PreparedStatement pstmt, int j,
                                    Column column) throws DataException,
      SQLException {
    if (column.getValue() != null)
      if (column.getType() == Column.Types.string)
        pstmt.setString(j, ( (String) column.getValue()).trim());
        //
      else if (column.getType() == Column.Types.integer)
        pstmt.setInt(j, ( (Integer) column.getValue()).intValue());
        //
      else if (column.getType() == Column.Types.number)
        pstmt.setDouble(j, ( (BigDecimal) column.getValue()).doubleValue());
      else if (column.getType() == Column.Types.time)
        pstmt.setTimestamp(j, (java.sql.Timestamp) column.getValue());
      else if (column.getType() == Column.Types.time_n)
        pstmt.setTimestamp(j, (java.sql.Timestamp) column.getValue());
      else
        throw new DataException("error.sql.invalidDataType");
  }

  private void updateColumn(ResultSet rs, Column column) throws DataException,
      SQLException {
    /*Alex: if want update a null column and it is nullable ,
     so we can save null value back to DB. following is original source code
         if (columnA.getValue()!=null)
        if (columnA.getType()==Column.Types.string)
     rs.updateString(columnA.getName(),((String)columnA.getValue()).trim());
        else if (columnA.getType()==Column.Types.number)
          rs.updateDouble(columnA.getName(),((BigDecimal)columnA.getValue()).doubleValue());
        else if (columnA.getType()==Column.Types.time)
     rs.updateTimestamp(columnA.getName(),(java.sql.Timestamp)columnA.getValue());
        else
          throw new DataException("error.sql.invalidDataType");
     */
    System.out.println("column name=" + column.getName()+" & column value=" + column.getValue());
    if (column.getValue() == null) {
      if (column.getNullable()) {
        rs.updateNull(column.getName());
      }
    }
    else {
      if (column.getType() == Column.Types.string)
        rs.updateString(column.getName(), ( (String) column.getValue()).trim());
      else if (column.getType() == Column.Types.integer)
        rs.updateInt(column.getName(), ( (Integer) column.getValue()).intValue());
      else if (column.getType() == Column.Types.number)
        rs.updateDouble(column.getName(),
                        ( (BigDecimal) column.getValue()).doubleValue());
      else if (column.getType() == Column.Types.time)
        rs.updateTimestamp(column.getName(),
                           (java.sql.Timestamp) column.getValue());
      else if (column.getType() == Column.Types.time_n)
        rs.updateTimestamp(column.getName(),
                           (java.sql.Timestamp) column.getValue());
      else
        throw new DataException("error.sql.invalidDataType");
    }
  }

  private Serializable getColumnValue(ResultSet rs, Column column) throws
      DataException, SQLException {

    if (column.getType() == Column.Types.string)
      return rs.getString(column.getName());
    else if (column.getType() == Column.Types.integer)
      return new Integer(rs.getInt(column.getName()));
    else if (column.getType() == Column.Types.number)
      return new Double(rs.getDouble(column.getName()));
    else if (column.getType() == Column.Types.time)
      return rs.getTimestamp(column.getName());
    else if (column.getType() == Column.Types.time_n)
      return rs.getTimestamp(column.getName());
    else
      throw new DataException("error.sql.invalidDataType");
  }

  private void closeStatement() {
    try {
      if (rs != null)
        rs.close();
      if (stmt != null)
        stmt.close();
      if (pstmt != null)
        pstmt.close();
    }
    catch (Exception e) {
    }
  }
}
